package com.anhdt.dao;

import com.anhdt.utils.VUtils;
import com.anhdt.vo.PostVO;
import com.anhdt.vo.SearchResultVO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PostDAO extends BaseDAO {

    private static PostDAO topicDAO;

    private PostDAO() {
        super();
    }

    public static PostDAO getInstance() {
        if (topicDAO == null) {
            topicDAO = new PostDAO();
        }
        return topicDAO;
    }

    public List<PostVO> getListPostByCateID(int idCate) throws SQLException {
        List<PostVO> list = new ArrayList<>();
        String sql = "Select * from post where categories_id =? order by position";
        try (Connection conn = connect.getConnnection()) {
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, idCate);
            try (ResultSet res = stmt.executeQuery()) {
                while (res.next()) {
                    PostVO tmp = new PostVO();
                    int id = res.getInt("id");
                    String name = res.getString("title");
                    tmp.setId(id);
                    tmp.setName(name);
                    list.add(tmp);
                }
            }
        }
        return list;
    }

    public PostVO getPostDetailById(int postId) throws SQLException {
        PostVO post = new PostVO();
        String sql = "select p.id,p.title,p.date,p.content,u.first_name,u.last_name from post as p inner join  user as u on p.user_id= u.id where p.id=?";
        try (Connection conn = connect.getConnnection()) {
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, postId);
            try (ResultSet res = stmt.executeQuery()) {
                if (res.next()) {
                    post.setId(res.getInt("id"));
                    post.setName(res.getString("title"));
                    post.setContent(res.getString("content"));
                    post.setAuthor(res.getString("first_name") + " " + res.getString("last_name"));
                    post.setDate(res.getDate("date").getTime());
                }
            }
             conn.close();
        }
       
        return post;
    }

    public List<PostVO> getListPostForIndex() throws SQLException {
        List<PostVO> list = new ArrayList<>();
        String sql = "select * from post order by date desc limit 5";
        try (Connection conn = connect.getConnnection()) {
            PreparedStatement stmt = conn.prepareStatement(sql);
            try (ResultSet res = stmt.executeQuery()) {
                while (res.next()) {
                    PostVO tmp = new PostVO();
                    int id = res.getInt("id");
                    String name = res.getString("title");
                    String content = res.getString("content");
                    if (content.length() > 300) {
                        content = content.substring(0, 200);
                        content = content.substring(0, content.lastIndexOf(" "));
                    }
                    tmp.setId(id);
                    tmp.setName(name);
                    tmp.setContent(VUtils.stripTag(content));
                    tmp.setCategoriesId(res.getInt("categories_id"));
                    list.add(tmp);
                }
            }
             conn.close();
            
        }
        return list;
    }

    public SearchResultVO search(String key, int pageNumber) throws SQLException {
        SearchResultVO search = new SearchResultVO();
        try (Connection conn = connect.getConnnection()) {
            String total = "SELECT COUNT(*) AS total FROM post WHERE title LIKE N?";
            ResultSet res;
            try (PreparedStatement stmtTotal = conn.prepareStatement(total)) {
                stmtTotal.setString(1, "%" + key + "%");
                System.out.println(stmtTotal);
                res = stmtTotal.executeQuery();
                if (res.next()) {
                    int totalRs = res.getInt("total");
                    search.setTotal(totalRs);
                }
            }
            res.close();
            System.out.println(search.getTotal());
            if (search.getTotal() > 0) {
                List<PostVO> list = new ArrayList<>();
                int Start = (pageNumber - 1) * 5;
                String sqlList = "SELECT *  FROM post WHERE title LIKE  N? limit "+Start+",5";
                try (PreparedStatement stmtPostList = conn.prepareStatement(sqlList)) {
                    stmtPostList.setString(1, "%" + key + "%");
                    try (ResultSet res2 = stmtPostList.executeQuery()) {
                        while (res2.next()) {
                            PostVO tmp = new PostVO();
                            int id = res2.getInt("id");
                            String name = res2.getString("title");
                            String content = res2.getString("content");
                            if (content.length() > 300) {
                                content = content.substring(0, 200);
                                content = content.substring(0, content.lastIndexOf(" "));
                            }
                            tmp.setId(id);
                            tmp.setName(name);
                            tmp.setContent(VUtils.stripTag(content));
                            tmp.setCategoriesId(res2.getInt("categories_id"));
                            list.add(tmp);
                        }
                    }
                }
                System.out.println(conn);
                conn.close();
                search.setListPost(list);
            }
        }
        return search;
    }

}
